package org.jeecg.modules.message.job;

import lombok.extern.slf4j.Slf4j;
import org.jeecg.modules.demo.pcl.entity.PclMinmaxMoniter;
import org.jeecg.modules.demo.pcl.mapper.PclMinmaxMoniterMapper;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

/**
 * 发送消息任务
 * @author: jeecg-boot
 */

@Slf4j
public class MinMaxMoniterJob implements Job {
	@Autowired
	public PclMinmaxMoniterMapper pclMinmaxMoniterMapper;
	@Override
	public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
		// 设置默认时区为本地时区
		TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai")); // 根据实际情况设置时区

		// 注册 JDBC 驱动程序
		try {
			Class.forName("com.ibm.as400.access.AS400JDBCDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return;
		}

		// 数据库连接配置
		String url = "jdbc:as400://10.224.80.16;naming=system;libraries=S78AF550;translate binary=true;";
		String user = "Hand_mes";
		String password = "Hand_mes@";

		try (Connection conn = DriverManager.getConnection(url, user, password)) {
			System.out.println("Connected to the DB2 database successfully!");

			// 清空表 PclMinmaxMoniter
//			try (Statement stmt = conn.createStatement()) {
//				stmt.executeUpdate("DELETE FROM pcl_minmax_moniter"); // 清空表
//			}
			pclMinmaxMoniterMapper.deleteAll();

			// 获取结果集
			try (Statement stmt = conn.createStatement()) {
				try (ResultSet rs = stmt.executeQuery(
						"SELECT s.AVPART, s.AVDES1, 'CHCS10' AS STOCK_TYPE, " +
								"s2.V6MAXQ, s2.V6MINQ, s3.BXQTOH, " +
								"CASE " +
								"WHEN s3.BXQTOH < s2.V6MINQ THEN '99' " +
								"WHEN s3.BXQTOH > s2.V6MAXQ THEN '95' " +
								"ELSE '5' END AS COLOR " +
								"FROM CHCDAT.STKMM s " +
								"INNER JOIN CHCDAT.STKA s2 ON s2.V6PART = s.AVPART AND s2.V6MINQ != 0 AND s2.V6MAXQ != 0 " +
								"INNER JOIN CHCDAT.STKB s3 ON s3.BXPART = s2.V6PART AND s3.BXSTOK = 'CHCS10' " +
								"ORDER BY CASE " +
								"WHEN s3.BXQTOH < s2.V6MINQ THEN 1 " + // 红色
								"WHEN s3.BXQTOH > s2.V6MAXQ THEN 2 " + // 黄色
								"ELSE 3 END" // 无色
				)) {
					List<Record> records = new ArrayList<>();
					while (rs.next()) {
						String avpart = new String(rs.getBytes("AVPART"), "Cp1047");
						String avdes1 = new String(rs.getBytes("AVDES1"), "Cp1047");
						String stockType = rs.getString("STOCK_TYPE");
						int v6maxq = rs.getInt("V6MAXQ");
						int v6minq = rs.getInt("V6MINQ");
						int bxqtoh = rs.getInt("BXQTOH");
						String color = rs.getString("COLOR");

						records.add(new Record(avpart, avdes1, stockType, v6maxq, v6minq, bxqtoh, color));
					}

					// 将数据插入到新表中
					for (Record record : records) {
						PclMinmaxMoniter domain = new PclMinmaxMoniter();
						domain.setAvpart(record.avpart);
						domain.setAvdes1(record.avdes1);
						domain.setStocktype(record.stockType);
						domain.setV6maxq(BigDecimal.valueOf(record.v6maxq));
						domain.setV6minq(BigDecimal.valueOf(record.v6minq));
						domain.setBxqtoh(BigDecimal.valueOf(record.bxqtoh));
						domain.setColor(record.color); // 设置颜色
						pclMinmaxMoniterMapper.insert(domain);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	// 添加 Record 类来存储查询结果
	private static class Record {
		String avpart;
		String avdes1;
		String stockType;
		int v6maxq;
		int v6minq;
		int bxqtoh;
		String color; // 添加 color 字段

		Record(String avpart, String avdes1, String stockType, int v6maxq, int v6minq, int bxqtoh ,String color) {
			this.avpart = avpart;
			this.avdes1 = avdes1;
			this.stockType = stockType;
			this.v6maxq = v6maxq;
			this.v6minq = v6minq;
			this.bxqtoh = bxqtoh;
			this.color = color; // 初始化 color
		}
	}
}
